package orz.itrip.mapper;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import orz.itrip.pojo.Comment;
import orz.itrip.vo.CommentAvgScoreVo;
import orz.itrip.vo.CommentCountVo;
import tk.mybatis.mapper.common.Mapper;

public interface CommentMapper extends Mapper<Comment> {
    @Select("SELECT CAST(AVG(ic.facilitiesScore) AS DECIMAL(10,1)) avgFacilitiesScore,CAST(AVG(ic.hygieneScore)  AS DECIMAL(10,1)) avgHygieneScore,CAST(AVG(ic.positionScore) AS DECIMAL(10,1)) avgPositionScore,CAST(AVG(ic.serviceScore) AS DECIMAL(10,1)) avgServiceScore,CAST(AVG(ic.score) AS DECIMAL(10,1)) avgScore FROM itrip_comment ic GROUP BY hotelId HAVING ic.hotelId=#{hotelId}")
    CommentAvgScoreVo selectAvgScore(@Param("hotelId") Long hotelId);
    @Select("SELECT\n" +
            "\t\t(SELECT COUNT(isOk) FROM itrip_comment WHERE isOk=1 GROUP BY hotelId HAVING hotelId=ic.hotelId) isok,\n" +
            "\t\t(SELECT COUNT(1) FROM itrip_comment GROUP BY hotelId HAVING hotelId=ic.hotelId) allcomment,\n" +
            "\t\t(SELECT COUNT(isOk) FROM itrip_comment WHERE isOk=0 GROUP BY hotelId HAVING hotelId=ic.hotelId) improve,\n" +
            "\t\t(SELECT COUNT(isHavingImg) FROM itrip_comment WHERE isHavingImg=1 GROUP BY hotelId HAVING hotelId=ic.hotelId) havingimg\n" +
            "FROM itrip_comment ic WHERE ic.hotelId=#{hotelId} GROUP BY ic.hotelId")
    CommentCountVo selectCountByComment(@Param("hotelId") Long hotelId);
}
